﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CRMFramework;
using System.Collections;
using Newtonsoft.Json;
using CRMEntity;
using System.IO;
using System.Web;

namespace CRMBiz.AM
{
    public class LoanContractHandler
    {

        public static DataSet SelectDataForLoanContract(int appid)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@p_appID",appid)
                };
                ds = DBhelper.ExecuteDataset("Select *,udf_getformatedDate(CreatedDate) as DraftDate from crm_contracttemp where app_id=@p_appID", para);
                if (ds.Tables[0].Rows.Count == 0)
                {
                    var strSql = new StringBuilder("SELECT c.ContactID,c.FirstName,c.MiddleName,c.SurName, c.DateBirth ,c.DriverLicense ,c.StreetNo ,c.Street ,c.Suburb ,c.State ,c.PostCode ,'1' AS DefaultData ,a.DesiredAmount,a.OfferedAmount,a.mc_preferred_loan_period,'20' AS EstablishFee,'4' AS MonthlyFee,CURRENT_DATE AS SettleDate, DATE_ADD(CURRENT_DATE,INTERVAL 1 DAY) AS firstpaymentDate ")
                                .Append(" FROM crm_contact c INNER JOIN  crm_applicationcontact ac  ON c.ContactID=ac.ContactID INNER JOIN crm_application a ON a.app_id=ac.app_id WHERE ac.app_id=@p_appID");
                    ds = DBhelper.ExecuteDataset(strSql.ToString(), para);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        public static DataSet SelectLoanContract(int LoanContractID)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@p_LoanContratcID",LoanContractID)
                };
                ds = DBhelper.ExecuteDataset("Select * from crm_loancontract where LoanContractID=@p_LoanContratcID", para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static string getDataForApplicationContractTemplate(int appid, int LoanContractID, double loanAmount, double establishFee, double monthlyFee, string settleDate, string paymentDate, int repaymentBy, int term)
        {
            string htmlFileName = string.Empty;
            try
            {
                string newHtmlString, htmlString, htmlFilePath, RepaymentBy = string.Empty;
                DateTime settlementDate = Convert.ToDateTime(settleDate);
                DateTime firstPaymentDate = Convert.ToDateTime(paymentDate);

                var para = new[] {
                    DBhelper.SetParameter("@p_LoanContratcID",LoanContractID),
                    DBhelper.SetParameter("@p_appID",appid)
                };
                
                DataSet ds = DBhelper.ExecuteDataset("select * from crm_loancontract where LoanContractID=@p_LoanContratcID", para);
                
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    htmlString = File.ReadAllText(System.Web.HttpContext.Current.Server.MapPath("/Pages/Application/MCData/Contract/SACCContractTemplate.html"));
                    newHtmlString = htmlString;

                    double totEstablishmentFee = CalculateInterest.SACC.GetTotalEstablishmentFee(loanAmount, establishFee);
                    double totMonthlyFee = CalculateInterest.SACC.GetTotalMonthlyFee(loanAmount, term, repaymentBy, monthlyFee, settlementDate, firstPaymentDate);

                    DataTable dtSchedule = CalculateInterest.SACC.SACCCalculateByTerm(loanAmount, term, repaymentBy, totEstablishmentFee, totMonthlyFee, settlementDate, firstPaymentDate);

                    if (ds.Tables[0].Rows[0]["RepaymentBy"].ToString() == "7")
                        RepaymentBy = "Week";
                    else if (ds.Tables[0].Rows[0]["RepaymentBy"].ToString() == "14")
                        RepaymentBy = "Fortnight";
                    else if (ds.Tables[0].Rows[0]["RepaymentBy"].ToString() == "30")
                        RepaymentBy = "Month";
                    newHtmlString = newHtmlString.Replace("%Name%", ds.Tables[0].Rows[0]["FirstName"].ToString() + " " + ds.Tables[0].Rows[0]["LastName"].ToString());

                    if (dtSchedule != null && dtSchedule.Rows.Count > 0)
                    {
                        newHtmlString = newHtmlString.Replace("%Repayment%", dtSchedule.Rows[0]["Repayment"].ToString())
                        .Replace("%Dateoffinalrepayment%", DateTime.Parse(dtSchedule.Rows[dtSchedule.Rows.Count - 1]["DueDate"].ToString()).ToShortDateString());
                    }
                    else
                    {
                        newHtmlString = newHtmlString.Replace("%Repayment%", "")
                        .Replace("%Dateoffinalrepayment%", "");
                    }

                    double v_LoanAmount = Convert.ToDouble(ds.Tables[0].Rows[0]["LoanAmount"].ToString());
                    double v_EstablishFee = Convert.ToDouble(ds.Tables[0].Rows[0]["EstablishFee"].ToString()) / 100;
                    double v_MonthlyFee = Convert.ToDouble(ds.Tables[0].Rows[0]["MonthlyFee"].ToString()) / 100;
                    double v_Assertainablefee = (v_EstablishFee * loanAmount) + (v_MonthlyFee * loanAmount);
                    int v_Termbymonth = Convert.ToInt32(ds.Tables[0].Rows[0]["TermbyMonth"].ToString());

                    double totPermittedFees = (v_EstablishFee * loanAmount) + (v_MonthlyFee * loanAmount) + (v_MonthlyFee * v_LoanAmount * (v_Termbymonth - 1));
                    decimal directDebitFees = (decimal.Parse("1.10") * decimal.Parse(ds.Tables[0].Rows[0]["Term"].ToString()));

                    double totAmount = loanAmount + totPermittedFees + Convert.ToDouble(directDebitFees);

                    string stremail = DBhelper.ExecuteScalar("Select Email from crm_contact where ContactID=" + ds.Tables[0].Rows[0]["ContactID"].ToString());

                    newHtmlString = newHtmlString.Replace("%Address%", ds.Tables[0].Rows[0]["StreetNo"].ToString() + " " + ds.Tables[0].Rows[0]["Street"].ToString() + " " + ds.Tables[0].Rows[0]["Suburb"].ToString() + " " + ds.Tables[0].Rows[0]["State"].ToString() + " " + ds.Tables[0].Rows[0]["PostCode"].ToString())
                        .Replace("%Email%", stremail)
                    .Replace("%LoanAmount%", Math.Round(decimal.Parse(ds.Tables[0].Rows[0]["LoanAmount"].ToString()), 2).ToString())
                    .Replace("%LoanNo%", "")
                    .Replace("%ContractDate%", DateTime.Parse(ds.Tables[0].Rows[0]["CreatedDate"].ToString()).ToShortDateString())
                    .Replace("%Establishmentfee%", (v_EstablishFee * v_LoanAmount).ToString())
                    .Replace("%Firstmonthlyfee%", (v_MonthlyFee * v_LoanAmount).ToString())
                    .Replace("%AscertainableFees%", v_Assertainablefee.ToString())
                    .Replace("%TotalLoanAmount%", (v_LoanAmount + v_Assertainablefee).ToString())
                    .Replace("%NoOfMonthAfterFirstFee%", (v_Termbymonth - 1).ToString())
                    .Replace("%OtherPermittedMonthlyFees%", (v_MonthlyFee * v_LoanAmount).ToString())
                    .Replace("%OtherMonthlyFee%", (v_MonthlyFee * v_LoanAmount * (v_Termbymonth - 1)).ToString())
                    .Replace("%TotalPermittedFeesPayable%", totPermittedFees.ToString())
                    .Replace("%TotalPermittedDirectDebitFees%", (directDebitFees).ToString())
                    .Replace("%SettlementDate%", DateTime.Parse(ds.Tables[0].Rows[0]["SettleDate"].ToString()).ToShortDateString())
                    .Replace("%Dateoffirstrepayment%", DateTime.Parse(ds.Tables[0].Rows[0]["firstPaymentDate"].ToString()).ToShortDateString())
                    .Replace("%Netloanpayable%", v_LoanAmount.ToString())
                    .Replace("%BSB%", ds.Tables[0].Rows[0]["BSB"].ToString())
                    .Replace("%TOTALAMOUNTTOBEREPAID%", totAmount.ToString())
                    .Replace("%AccountName%", ds.Tables[0].Rows[0]["AccountName"].ToString())
                    .Replace("%AccountNo%", ds.Tables[0].Rows[0]["AccountNo"].ToString())
                    .Replace("%Amount%", ds.Tables[0].Rows[0]["LoanAmount"].ToString())
                    .Replace("%Terms%", ds.Tables[0].Rows[0]["Term"].ToString())
                    .Replace("%TermInMonths%", ds.Tables[0].Rows[0]["TermbyMonth"].ToString())
                    .Replace("%Paid%", RepaymentBy)
                    .Replace("%term%", ds.Tables[0].Rows[0]["Term"].ToString())
                    .Replace("%termbyMonth%", ds.Tables[0].Rows[0]["TermbyMonth"].ToString())
                    .Replace("%NumberofRepayments%", ds.Tables[0].Rows[0]["Term"].ToString());
                    htmlFileName = "Con" + DateTime.Now.ToString("yyyyMMddhhmmss") + "_" + ds.Tables[0].Rows[0]["LastName"].ToString() + "_" + ds.Tables[0].Rows[0]["FirstName"].ToString() + "_" + appid + ".html";
                    htmlFilePath = System.Web.HttpContext.Current.Server.MapPath("~/") + "Pages\\Application\\MCData\\Contract\\" + htmlFileName;

                    System.IO.File.WriteAllText(htmlFilePath, newHtmlString);

                    DBhelper.ExecuteNonQuery(string.Format("UPDATE crm_loancontract SET FilePath='{0}' Where LoanContractID=@p_LoanContratcID", htmlFileName), para);

                    DataSet dsHistory = DBhelper.ExecuteDataset("SELECT Description,HistoryID FROM crm_history WHERE app_id=@p_appID and action='Contract Created' ORDER BY HistoryID DESC LIMIT 1", para);

                    var strDescription = new StringBuilder("<b> Contract Generated : </b><br /><a href=\"/Pages/Application/MCData/Contract/")
                        .AppendFormat("{0} \" target=\"_blank\"> View Contract </a><br/> {1}", htmlFileName, dsHistory.Tables[0].Rows[0]["Description"].ToString());

                    var paraHistory = new[]{
                        DBhelper.SetParameter("@p_HistoryID", dsHistory.Tables[0].Rows[0]["HistoryID"]),
                        DBhelper.SetParameter("@p_Description",strDescription.ToString())
                    };
                    DBhelper.ExecuteNonQuery("UPDATE crm_history SET Description=@p_Description Where HistoryID=@p_HistoryID", paraHistory);
                }
                else
                    htmlFileName = "SACCContractTemplate.html";
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return htmlFileName;
        }

        public static DataSet FillContractHistory(int appid)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@p_appID",appid)
                };
                ds = DBhelper.ExecuteDataset("SELECT LoanContractID as ContractNo, udf_getformatedDate(CreatedDate) as Cdate, CreatedDate as Age, LoanAmount,LoanAmount ,AccountNo as AccNo,CONCAT(FirstName,' ',MiddleName,' ', LastName) AS cname,FilePath FROM crm_loancontract WHERE app_id=@p_appID ORDER BY CreatedDate Desc",para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static bool saveContractDraft(int app_id, int ContactID, double LoanAmount, double EstablishFee, double MonthlyFee, string SettleDate, string firstPaymentDate, string LastPaymentDate, int RepaymentBy, int Term, int TermbyMonth, string BankName, string BSB,
            string AccName, string AccNo, string FirstName, string MiddleName, string LastName, string DateBirth, string DriverLicense, string StreetNo, string Street, string Suburb, string State, string PostCode, int updateClientProfile, string historyOperator, string FilePath)
        {
            bool flag = false;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@p_app_id",app_id),
                    DBhelper.SetParameter("@p_ContactID",ContactID),
                    DBhelper.SetParameter("@p_FirstName",FirstName), 
                    DBhelper.SetParameter("@p_MiddleName",MiddleName),
                    DBhelper.SetParameter("@p_LastName",LastName),
                    DBhelper.SetParameter("@p_DateBirth",DateBirth), 
                    DBhelper.SetParameter("@p_DriverLicense",DriverLicense), 
                    DBhelper.SetParameter("@p_StreetNo",StreetNo), 
                    DBhelper.SetParameter("@p_Street",Street), 
                    DBhelper.SetParameter("@p_Suburb",Suburb), 
                    DBhelper.SetParameter("@p_State",State),
                    DBhelper.SetParameter("@p_PostCode",PostCode), 
                    DBhelper.SetParameter("@p_LoanAmount",LoanAmount),
                    DBhelper.SetParameter("@p_EstablishFee",EstablishFee),
                    DBhelper.SetParameter("@p_MonthlyFee",MonthlyFee),
                    DBhelper.SetParameter("@p_SettleDate",SettleDate),
                    DBhelper.SetParameter("@p_firstPaymentDate",firstPaymentDate),
                    DBhelper.SetParameter("@p_LastPaymentDate",LastPaymentDate),
                    DBhelper.SetParameter("@p_RepaymentBy",RepaymentBy),
                    DBhelper.SetParameter("@p_Term",Term),
                    DBhelper.SetParameter("@p_TermbyMonth",TermbyMonth),
                    DBhelper.SetParameter("@p_BankName",BankName),
                    DBhelper.SetParameter("@p_BSB",BSB),
                    DBhelper.SetParameter("@p_AccName",AccName),
                    DBhelper.SetParameter("@p_AccNo",AccNo),
                    DBhelper.SetParameter("@p_CreatedBy",historyOperator),
                    DBhelper.SetParameter("@p_FilePath",FilePath),
                    DBhelper.SetParameter("@p_updateClientProfile",updateClientProfile) 
                };

                DBhelper.ExecuteNonQuerySP("sp_saveContractDraft", para);
                flag = true;

            }
            catch (Exception ex)
            {

                throw ex;
            }
            return flag;

        }

        public static string generateContract(int app_id, int ContactID, double LoanAmount, double EstablishFee, double MonthlyFee, string SettleDate, string firstPaymentDate, string LastPaymentDate, int RepaymentBy, int Term, int TermbyMonth, decimal repaymentAmt, decimal totalEstFee, decimal totalmonthFee, string BankName, string BSB,
            string AccName, string AccNo, string FirstName, string MiddleName, string LastName, string DateBirth, string DriverLicense, string StreetNo, string Street, string Suburb, string State, string PostCode, string historyOperator, int updateClientProfile, string FilePath)
        {
            int LoanContractID;
            string ContractFileName = string.Empty;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@p_app_id",app_id),
                    DBhelper.SetParameter("@p_ContactID",ContactID),
                    DBhelper.SetParameter("@p_FirstName",FirstName), 
                    DBhelper.SetParameter("@p_MiddleName",MiddleName),
                    DBhelper.SetParameter("@p_LastName",LastName), 
                    DBhelper.SetParameter("@p_DateBirth",DateBirth), 
                    DBhelper.SetParameter("@p_DriverLicense",DriverLicense), 
                    DBhelper.SetParameter("@p_StreetNo",StreetNo), 
                    DBhelper.SetParameter("@p_Street",Street), 
                    DBhelper.SetParameter("@p_Suburb",Suburb), 
                    DBhelper.SetParameter("@p_State",State),
                    DBhelper.SetParameter("@p_PostCode",PostCode), 
                    DBhelper.SetParameter("@p_LoanAmount",LoanAmount),
                    DBhelper.SetParameter("@p_EstablishFee",EstablishFee),
                    DBhelper.SetParameter("@p_MonthlyFee",MonthlyFee),
                    DBhelper.SetParameter("@p_SettleDate",SettleDate),
                    DBhelper.SetParameter("@p_firstPaymentDate",firstPaymentDate),
                    DBhelper.SetParameter("@p_LastPaymentDate",LastPaymentDate),
                    DBhelper.SetParameter("@p_RepaymentBy",RepaymentBy),
                    DBhelper.SetParameter("@p_Term",Term),
                    DBhelper.SetParameter("@p_TermbyMonth",TermbyMonth),
                    DBhelper.SetParameter("@p_PaymentRegular",repaymentAmt),
                    DBhelper.SetParameter("@p_totalEstFee",totalEstFee),
                    DBhelper.SetParameter("@p_totalmonthFee",totalmonthFee),
                    DBhelper.SetParameter("@p_BankName",BankName),
                    DBhelper.SetParameter("@p_BSB",BSB),
                    DBhelper.SetParameter("@p_AccName",AccName),
                    DBhelper.SetParameter("@p_AccNo",AccNo),
                    DBhelper.SetParameter("@p_CreatedBy",historyOperator),
                    DBhelper.SetParameter("@p_updateClientProfile",updateClientProfile),
                    DBhelper.SetParameter("@p_FilePath",FilePath),
                };

                DBhelper.ExecuteNonQuerySP("sp_generateContract", para);
                LoanContractID = Convert.ToInt16(DBhelper.ExecuteScalar("Select MAX(LoanContractID) From crm_loancontract"));
                ContractFileName = getDataForApplicationContractTemplate(app_id, LoanContractID, LoanAmount, EstablishFee, MonthlyFee, SettleDate, firstPaymentDate, RepaymentBy, Term);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return (LoanContractID.ToString() + "#" + ContractFileName);
        }

        public static bool createLoan(int app_id, int ContactID, double LoanAmount, double EstablishFee, double MonthlyFee, string SettleDate, string firstPaymentDate, int RepaymentBy, int Term, string BankName, string BSB, string FirstName, string MiddleName, string LastName, string historyOperator, string FilePath)
        {
            bool flag = false;
            try
            {
                var para = new[]{
                    DBhelper.SetParameter("@p_app_id",app_id),
                    DBhelper.SetParameter("@p_ContactID",ContactID),
                    DBhelper.SetParameter("@p_FirstName",FirstName), 
                    DBhelper.SetParameter("@p_MiddleName",MiddleName),
                    DBhelper.SetParameter("@p_LastName",LastName), 
                    DBhelper.SetParameter("@p_LoanAmount",LoanAmount),
                    DBhelper.SetParameter("@p_EstablishFee",EstablishFee),
                    DBhelper.SetParameter("@p_MonthlyFee",MonthlyFee),
                    DBhelper.SetParameter("@p_SettleDate",SettleDate),
                    DBhelper.SetParameter("@p_firstPaymentDate",firstPaymentDate),
                    DBhelper.SetParameter("@p_RepaymentBy",RepaymentBy),
                    DBhelper.SetParameter("@p_Term",Term),
                    DBhelper.SetParameter("@p_BankName",BankName),
                    DBhelper.SetParameter("@p_BSB",BSB),
                    DBhelper.SetParameter("@p_CreatedBy",historyOperator),
                    DBhelper.SetParameter("@p_FilePath",FilePath),
                };
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}
